/******************************************************************************
* Merge financial distress measures with demographics
* Produce summary statistics and maps
*****************************************************************************/





*****************************************************************************
**  load TU data from spark server and merge with covariates
*****************************************************************************

*set up globals based on geo level
geoLevel
	
* cz level csv file from spark
import delimited using "${dataRoot}/sumstats_${sumStatsyr}${sumStatsM}_indiToCZ.csv", ///
	delimiters(",") varnames(1) numericcols(${numCol}/99) clear

*call program to clean up variables and perform some calculation
cleanUpVars ${GeoCZ}

* capitalize first letter, long label
labelVars 1 1

* merge financial distress measure from TU and demogrpahics in ACS
merge 1:1 ${Geo} using "${dataRoot}/acs_2015_${Geo}"
drop _merge

* merge state level legal variables
// rename stateabbr state
merge m:1 state using  "${proj}/Data/Raw/state_legal"
keep if _merge==3
drop _merge

label var mean_sa 	"Mean seizable asset"
label var median_sa "Median seizable asset"
label var p25_sa    "25th pct seizable asset"
label var p75_sa    "75th pct seizable asset"
label var wage_garnish    "Wage garnishment"
label var recourse "Recourse State"
label var judicial "Judicial State"

*re-arrange legal variables
gen nonjud  = (judicial==0)
gen garnish = 100*(1-wage_garnish)
label var nonjud    "Non-judicial states"
label var garnish   "Wage garnishment"


* merge bankruptcy fee variables
merge 1:1 cz using "${proj}/Data/Raw/TU_bankruptcy_fee"
rename _merge _merge_bkfee

drop chp*_dis

* avg of pre/post BAPCPA attorney fee, wtd by number of years in the pre/post periods
gen chp7fee  = 5/16 * chp7feepre  + 11/16 * chp7feepost
gen chp13fee = 5/16 * chp13feepre + 11/16 * chp13feepost
label var chp7fee  "Chapter 7 attorney fee"
label var chp13fee "Chapter 13 attorney fee"


* merge hospital variables
merge 1:1 cz using "${dataRoot}/TU_Geo_Debt_Hospital_2014"
rename _merge _merge_hospital

* merge banking variables
merge 1:1 cz using "${dataRoot}/TU_Geo_Debt_Banking_Access_2014"
rename _merge _merge_bank


* TU credit supply
merge 1:1 cz using "${dataRoot}/cz_credit_limit"
rename _merge _merge_tu


** convert monetary/count variables to log 
foreach var of varlist inc_median inc_mean house_median *_sa creditlimit_bal avail_credit{
	replace `var' = log(`var'+1)
}

*define North (upper midwest) vs South (deep south)
*this program generates variable "NvS"
defineNvS


*save
compress
save "${dataRoot}/tu_acs_${Geo}_${sumStatsyr}", replace

preserve
	keep cz NvS
	duplicates drop
	save "${dataRoot}/tu_cz_NvS.dta", replace
restore


	
********************************************************************************************************************
**  Maps of key TU variables (Figure 1, A2,A3,A5)
********************************************************************************************************************

use "${dataRoot}/tu_acs_${Geo}_${sumStatsyr}", clear
foreach var in $keyTuVars {
	if strpos("`var'","bal"){
		local dec = 0
	}
	else{
		local dec = 1
	}
	mapPlot `var' ${mapGeo}  "`var'_${Geo}_${sumStatsyr}" "" `dec'
}


*****************************************************************************
**  Maps of TU observations with credit report (Figure A4)
*****************************************************************************

use "${dataRoot}/tu_acs_${Geo}_${sumStatsyr}", clear
** number of osbervations with no trade accounts between 20 and 80
merge 1:1 cz using "${dataRoot}/June2015_count_cz", keep(3) nogen


*frac with credit card
gen hascc=100*(ccdq/ccdq_cond)
*frac 20 to 80 with credit report
gen hascredit28 = 100*numobs/total_pop20to80
*frac 20 to 80 with credit report but no trade accounts
gen hasnotrdcredit28 = 100*(total - has_trades)/total_pop20to80

* maps
mapPlot hascc            ${mapGeo}  "hascc_${Geo}_${sumStatsyr}" "" 1
mapPlot hascredit28      ${mapGeo}  "hascredit28_${Geo}_${sumStatsyr}" "" 1
mapPlot hasnotrdcredit28 ${mapGeo}  "hasnotrdcredit28_${Geo}_${sumStatsyr}" "" 1


	
	
********************************************************************************************************************
**  TU variables variation (Table 3)
********************************************************************************************************************
use "${dataRoot}/tu_acs_${Geo}_${sumStatsyr}", clear
	
* capitalize first letter, long label
labelVars 1 1

	
local clpav = " (mean) "
local clpsd = " (sd) "
local clpq1 = " (p25) "
local clpq3 = " (p75) "
local clpd1 = " (p10) "
local clpd9 = " (p90) "

local vn = 0
foreach var in $keyTuVars{
	local vn = `vn'+1
	
	*summary
	qui gen v_`vn'  = `var'
	qui gen v_s`vn' = `var' if NvS==0
	qui gen v_n`vn' = `var' if NvS==1
	

	*set up test for collapsing
	local clpav = " `clpav' av_`vn'=v_`vn' av_s`vn'=v_s`vn' av_n`vn'=v_n`vn'  "
	local clpsd = " `clpsd' sd_`vn'=v_`vn' "
	local clpq1 = " `clpq1' q1_`vn'=v_`vn' "
	local clpq3 = " `clpq3' q3_`vn'=v_`vn' "
	local clpd1 = " `clpd1' d1_`vn'=v_`vn' "
	local clpd9 = " `clpd9' d9_`vn'=v_`vn' "
	
	*store variable label string for output table
	local varStr`vn' = "\addlinespace[0.1em] \\ `:var l `var''"
	
	di "`varStr`vn' '"
	
}


*collapse statistics across geo
collapse `clpav' `clpsd' `clpq1' `clpq3' `clpd1' `clpd9' ${wtd},  fast
local vn1 = `vn'
forval vn=1/`vn1'{
	*inter-quartile 72-25 differences
	gen intq_`vn' = q3_`vn'  - q1_`vn'

	*inter-decile 90-10 differences
	gen intd_`vn' = d9_`vn'  - d1_`vn'
	
	*south minus north 
	gen nvs_`vn'  = av_s`vn' - av_n`vn'
}


drop q1_* q3_* d1_* d9_*



*reshape to long, each observation is a tu variable
gen ind=1
reshape long av_ sd_ av_s av_n nvs_ intq_ intd_  , i(ind)
rename _j tuvars


*variable name
gen varN = ""
forval vn=1/`vn1'{
	replace varN = "`varStr`vn''" if tuvars==`vn'
}


***** format into listtex tables
drop ind

*convert varibales into desired string format directly
tostring av_ sd_ av_s av_n nvs_ intq_ intd_, replace force format(%12.1fc)


*** output latex tables

local x1 "\phantom{x}"
local x2 "\phantom{xx}"

** subheaders

replace varN  = subinstr(varN,"\addlinespace[0.1em] \\"," \addlinespace[0.1em] \\ \hspace{5mm} \emph{Collections - Flow} &&&&&&& \\ \addlinespace[0.1em] \\",.) if tuvar==1

replace varN  = subinstr(varN,"\addlinespace[0.1em] \\","\addlinespace[0.1em] \\ \addlinespace[0.1em] \\ \hspace{5mm} \emph{Collections - Stock} &&&&&&& \\ \addlinespace[0.1em] \\",.) if tuvar==3

replace varN  = subinstr(varN,"\addlinespace[0.1em] \\","\addlinespace[0.1em] \\ \addlinespace[0.1em] \\ \hspace{5mm} \emph{Credit Card} &&&&&&& \\ \addlinespace[0.1em] \\",.) if tuvar==9
replace varN  = subinstr(varN,"\addlinespace[0.1em] \\","\addlinespace[0.1em] \\ \addlinespace[0.1em] \\ \hspace{5mm} \emph{Bankruptcy} &&&&&&& \\ \addlinespace[0.1em] \\",.) if tuvar==11


local texHead = "\begin{tabular}{ l ccccccc} \hline\hline  " + ///
			"  & (1)  &  (2)    & (3)    & (4)     & (5)    & (6)      & (7)           \\ " + ///
			"  &      &         & 75\% - & 90\% -  & Deep   & Upper    & Deep South -  \\ " + ///
			"  & Mean & Std.Dev.& 25\%   & 10\%    & South  & `x1'Midwest`x1'  & Upper Midwest \\ \hline"
listtex varN av_ sd_  intq_ intd_ av_s av_n nvs_ using "$outDir/Tex/variation_${Geo}_${sumStatsyr}.tex" ,replace ///
	rstyle(tabular)	 head("`texHead'") ///
	foot("\addlinespace[0.2em] \\ \hline\hline \end{tabular}")					

	
	
	
	

*****************************************************************************
**  collection breakdown (Table A1)
*****************************************************************************
use "${dataRoot}/coll_201506", clear
tostring pct*, replace force format(%12.3fc)
tostring avg*, replace force format(%12.1fc)

local texHead = "\begin{tabular}{ l ccc} \toprule  " + ///
			" Creditor Types &  \% Current Balance  & \% Number of Accounts   & Average Balance    \\ \midrule"
listtex * using "$outDir/Tex/collection_breakdown.tex" ,replace ///
	rstyle(tabular)	 head("`texHead'") ///
	foot(" \bottomrule \end{tabular}")	
	
	

*****************************************************************************
**  compare migration vs census (Table A2)
*****************************************************************************
*** acs and cps moving pattern
use "${dataRoot}/acs_migration_year", clear
merge 1:1 year using  "${dataRoot}/cps_migration_year", keep(3) nogen
save "${dataRoot}/census_migration", replace


** moving pattern from TU data
use "${dataRoot}/moving_200509", clear
gen year = 2005

append using "${dataRoot}/moving_201509"
replace year = 2015 if year ==.

merge 1:1 year using  "${dataRoot}/census_migration", keep(3) nogen

order year *

rename move_statefip_* move_state_*

* arrange cps moving pattern to be consistent with acs and tu
gen cps_move_1 = 1- mig_1y_same_hs
gen cps_move_5 = 1- mig_5y_same_hs

gen cps_move_county_1 = mig_1y_across_ct_within_st + mig_1y_across_st

gen cps_move_state_1 = mig_1y_across_st
gen cps_move_state_5 = mig_5y_across_st

drop mig_*


* rearrange data to generate table
rename moved_* acs_move_*_1
rename acs_move_any_1 acs_move_1

reshape long move cps_move acs_move, i(year) string

replace move = move*100
replace cps_move = cps_move*100
replace acs_move = acs_move*100
reshape wide move cps_move acs_move, i(_j) j(year)

gen year = 1
replace year = 5 if strpos(_j, "5")

gen move = " Any move"
replace move = "Across zipcode" if strpos(_j, "zip")
replace move = "Across county" if strpos(_j, "county")
replace move = "Across State" if strpos(_j, "state")

gen move_num = 1
replace move_num = 2 if strpos(_j, "zip")
replace move_num = 3 if strpos(_j, "county")
replace move_num = 4 if strpos(_j, "state")

replace move = "\addlinespace[0.1em] \\ \hspace{5mm} \emph{From 1 year ago} \\ " + move if year==1 & move_num==1
replace move = "\addlinespace[0.1em] \\ \hspace{5mm} \emph{From 5 years ago} \\ " + move if year==5 & move_num==1


sort year move_num

order move move2005 cps_move2005 acs_move2005 move2015 cps_move2015 acs_move2015
tostring *20*5, replace force format(%12.2fc)

local texHead = "\begin{tabular}{ l cccccc} \toprule  " + ///
			"  & 2005  &  2005 & 2005 & 2015  &  2015 &  2015   \\ " + ///
			" Pct of population (\%)  & TU    & CPS & ACS  & TU    & CPS & ACS    \\ \midrule"
listtex move move2005 cps_move2005 acs_move2005 move2015 cps_move2015 acs_move2015 using "$outDir/Tex/cps_benchmark_migration.tex" ,replace ///
	rstyle(tabular)	 head("`texHead'") ///
	foot(" \bottomrule \end{tabular}")	
	


		
	
